package br.unirio.ticketnator.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
//import java.sql.CallableStatement;
import java.sql.ResultSet;
//import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import br.unirio.ticketnator.model.Sistema;

public class SistemaDAO implements ISistemaDAO
{
	private Sistema carrega (ResultSet rs) throws SQLException
	{
		Sistema sistema = new Sistema();
		sistema.setIdSistema(rs.getInt("id"));
		sistema.setNomeSistema(rs.getString("nomesistema"));
		
		return sistema;
	}
	
	@Override
	public Sistema getSistema(int id)
	{
		Connection c = SupportDAO.getConnection();
		
		if(c == null)
			return null;
		Sistema sistema = null;
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT * FROM TB_SISTEMA WHERE ID_SISTEMA = id");
			ps.setLong(1, id);
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next())
				sistema = carrega(rs);
			c.close();
				
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return sistema;
	}
	
	@Override
	public int conta() 
	{
		Connection c = SupportDAO.getConnection();
		
		if(c == null)
			return 0;
		
		int numero = 0;
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT COUNT(*) FROM TB_SISTEMA");
			ResultSet rs = ps.executeQuery();
			
			if(rs.next())
				numero = rs.getInt(1);
			
			c.close();
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return numero;
	}
	
	@Override
	public List<Sistema> lista(int pagina, int tamanho) {
		
		Connection c = SupportDAO.getConnection();
		
		if(c == null)
			return null;
		
		List<Sistema> lista = new ArrayList<Sistema>();
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT * FROM TB_SISTEMA ORDER BY  NOME_SISTEMA LIMIT ? OFFSET ?");
			ps.setInt(1, tamanho);
			ps.setInt(2, pagina * tamanho);
			ResultSet rs = ps.executeQuery();
			
			while (rs.next())
				lista.add(carrega(rs));
			
			c.close();
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return lista;
	}
	
	@Override
	public boolean insereSistema(Sistema sistema)
	{
		
		Connection c = SupportDAO.getConnection();
		String insertSistema = "INSERT INTO TB_SISTEMA (NOME_SISTEMA) VALUES (?)";
		
		if (c == null)
			return false;
		
		try
		{
//			CallableStatement cs = c.prepareCall("{call InsereSistema(?, ?)}");
//			cs.setString(1, sistema.getNomeSistema());
//			cs.registerOutParameter(2, Types.INTEGER);
//			
//			int id = cs.getInt(2);
//			sistema.setIdSistema(id);
//			cs.execute();
			
			PreparedStatement ps = c.prepareStatement(insertSistema);
			ps.setString(1, sistema.getNomeSistema());
			ps.executeUpdate();
			
			c.close();
			return true;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return false;
		}
	}
	
	@Override
	public boolean atualizaSistema(Sistema sistema)
	{
		Connection c = SupportDAO.getConnection();
		String updateSistema = "UPDATE TB_SISTEMA SET NOME_SISTEMA = ? WHERE ID_SISTEMA = ?";
		
		if (c == null)
			return false;
		try
		{
//			CallableStatement cs = c.prepareCall("{call AtualizaSistema(?, ?)}");
//			cs.setInt(1, sistema.getIdSistema());
//			cs.setString(2, sistema.getNomeSistema());
//			cs.execute();
			
			PreparedStatement ps = c.prepareStatement(updateSistema);
			ps.setString(1, sistema.getNomeSistema());
			ps.setInt(2, sistema.getIdSistema());
			ps.executeUpdate();
			
			c.close();
			return true;
		
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return false;
		}
	}
	
	@Override
	public boolean removeSistema(int id) 
	{
		Connection c = SupportDAO.getConnection();
		String deleteSistema = "DELETE FROM TB_SISTEMA WHERE ID_SISTEMA = ?";
		
		if (c == null)
			return false;
		try
		{
//			CallableStatement cs = c.prepareCall("{call RemoveSistema(?)}");
//			cs.setInt(1, id);
//			cs.execute();
			PreparedStatement ps = c.prepareStatement(deleteSistema);
			ps.setInt(1, id);
			ps.executeUpdate();
			
			c.close();
			return true;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return false;
		}
	}


}
